<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<title>Using MySQL database in JSP</title>
<link rel="stylesheet" href="/cfg/format.css" type="text/css">
<meta http-equiv="content-type" content="text/html; charset=utf-8">
<meta name="keywords" content="jee tutorial, java, web applications, JSP">
<meta name="description" content="In this part of the Java EE 5 tutorials, we use a MySQL database in JSP.">
<meta name="language" content="en">
<meta name="author" content="Jan Bodnar">
<meta name="distribution" content="global">

<script type="text/javascript" src="/lib/jquery.js"></script>
<script type="text/javascript" src="/lib/common.js"></script>

</head>

<body>

<div class="container">

<div id="wide_ad" class="ltow">
<script type="text/javascript"><!--
google_ad_client = "pub-9706709751191532";
/* 160x600, August 2011 */
google_ad_slot = "2484182563";
google_ad_width = 160;
google_ad_height = 600;
//-->
</script>
<script type="text/javascript"
src="http://pagead2.googlesyndication.com/pagead/show_ads.js">
</script>
</div>

<div class="content">


<a href="/" title="Home">Home</a>&nbsp;
<a href="..">Contents</a>


<h1>JSP &amp; MySQL Database</h1>


<p>
In this part of the jee 5 tutorials, we will work with MySQL database.
</p>

<div class="center"> 
<script type="text/javascript"><!--
google_ad_client = "pub-9706709751191532";
/* horizontal */
google_ad_slot = "1734478269";
google_ad_width = 468;
google_ad_height = 60;
//-->
</script> 
<script type="text/javascript"
src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> 
</script> 
</div> 

<p>
MySQL is a leading open source database management system. It is a multi 
user, multithreaded database management system. MySQL is especially 
popular on the web. Recently, the company that developed MySQL database was bought by
Sun.
</p>

<h2>Starting and stopping MySQL</h2>

<pre class="code">
$ sudo /etc/init.d/mysql start
</pre>

<p>
Starting the MySQL database server.
</p>


<pre class="code">
$ sudo /etc/init.d/mysql stop
</pre>

<p>
Stopping the MySQL database server.
</p>

<pre class="code">
$ sudo mysqladmin -p ping
Password:
Enter password: 
mysqld is alive
</pre>

<p>
Checking, if the server is running. We typed two passwords. One for the 
sudo command and one for the mysqladmin command.
</p>


<h2>Creating a database</h2>

<p>
In this example, we will create an empty database. The example consists of 
two jsp files. The index.jsp and error.jsp.
The index.jsp creates a new database or reports an error in error.jsp file. 
</p>

<div class="codehead">index.jsp</div>
<pre class="code">
&lt;%@ page contentType="text/html;charset=UTF-8" %&gt;
&lt;%@ page errorPage="error.jsp" %&gt;
&lt;%@ page import="java.sql.*" %&gt;

&lt;html&gt;
&lt;head&gt;
&lt;title&gt;MySQL Database creation&lt;/title&gt;
&lt;style&gt;
* { font-size: 12px; font-family: Verdana }
&lt;/style&gt;
&lt;/head&gt;
&lt;body&gt;

&lt;h2&gt;Creation of a books database&lt;/h2&gt;

&lt;jsp:declaration&gt;

Statement stmt;
Connection con;
String url = "jdbc:mysql://localhost:3306/";

&lt;/jsp:declaration&gt;

&lt;jsp:scriptlet&gt;&lt;![CDATA[

Class.forName("com.mysql.jdbc.Driver");
con = DriverManager.getConnection(url, "root", ""); 

stmt = con.createStatement();
stmt.executeUpdate("CREATE DATABASE books");
con.close();

]]&gt;&lt;/jsp:scriptlet&gt;

&lt;/body&gt;
&lt;/html&gt;
</pre>

<p>
This jsp page connects to a server and creates a books database. If we refresh 
the page again, we get an error message
and an error.jsp page is loaded. We cannot create two databases with a same name.
</p>


<pre class="explanation">
&lt;%@ page errorPage="error.jsp" %&gt;
</pre>

<p>
If an Exception occurs in page, the server loads an error.jsp page.
</p>

<pre class="explanation">
 &lt;jsp:declaration&gt;

 Statement stmt;
 Connection con;
 String url = "jdbc:mysql://localhost:3306/";

 &lt;/jsp:declaration&gt;
</pre>

<p>
Here we declare a <b>Statement</b> a <b>Connection</b> and a connection url. 
The connection url consists of a protocol, host and a port number. The default 
MySQL port number is 3306.
</p>

<pre class="explanation">
Class.forName("com.mysql.jdbc.Driver");
</pre>

<p>
We register a MySQL JDBC driver.
</p>

<pre class="explanation">
con = DriverManager.getConnection(url, "root", ""); 
</pre>

<p>
We get a connection to the database for user root with blank password. 
This is the default admin account on MySQL. 
</p>

<pre class="explanation">
stmt = con.createStatement();
stmt.executeUpdate("CREATE DATABASE books");
</pre>

<p>
We get a Statement object and execute an SQL query. 
</p>

<pre class="explanation">
con.close();
</pre>

<p>
Finally we close the connection.
</p>

<div class="codehead">error.jsp</div>
<pre class="code">
&lt;%@ page contentType="text/html" pageEncoding="UTF-8"%&gt;
&lt;%@ page isErrorPage="true" %&gt;

&lt;html&gt;
&lt;head&gt;
&lt;title&gt;Error page&lt;/title&gt;
&lt;style&gt;
    * { font-size: 12px; font-family: Verdana }
&lt;/style&gt;
&lt;/head&gt;
&lt;body&gt;
&lt;h2&gt;Error occured!&lt;/h2&gt;
&lt;p&gt;Message &lt;jsp:expression&gt; exception.getMessage() &lt;/jsp:expression&gt;

&lt;/body&gt;
&lt;/html&gt;
</pre>

<p>
This is our error page. 
</p>

<pre class="explanation">
&lt;%@ page isErrorPage="true" %&gt;
</pre>

<p>
This directive enables an <b class="exception">exception</b> object for a page.
</p>


<pre class="explanation">
&lt;p&gt;Message &lt;jsp:expression&gt; exception.getMessage() &lt;/jsp:expression&gt;
</pre>

<p>
Here we print the error message.
</p>


<img src="/img/java/jee/error.png" alt="Error page">
<div class="figure">Figure: Error page</div>


<pre class="code">
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema | 
| books              | 
| mysql              | 
| testdb             | 
+--------------------+
4 rows in set (0.12 sec)
</pre>

<p>
We can check from the mysql command line tool, if the database was created.
</p>


<h2>Books</h2>

<p>
The following example will be a more complex application. We will be adding and deleting
items.
</p>


<pre class="code">
mysql> create table books(id int not null primary key auto_increment, 
              author varchar(30), title varchar(40),
              year int, remark varchar(100));
Query OK, 0 rows affected (0.13 sec)
</pre>


<p>
We create a books table first. The table will have five columns. 
The id of the row, author name, title of the book,
year of publishing and a small remark conserning the book.
</p>

<pre class="code">

mysql> describe books;
+--------+--------------+------+-----+---------+----------------+
| Field  | Type         | Null | Key | Default | Extra          |
+--------+--------------+------+-----+---------+----------------+
| id     | int(11)      | NO   | PRI | NULL    | auto_increment | 
| author | varchar(30)  | YES  |     | NULL    |                | 
| title  | varchar(40)  | YES  |     | NULL    |                | 
| year   | int(11)      | YES  |     | NULL    |                | 
| remark | varchar(100) | YES  |     | NULL    |                | 
+--------+--------------+------+-----+---------+----------------+
5 rows in set (0.22 sec)
</pre>

<p>
Here we see, how the table looks like using the mysql command line tool.
</p>

<p>
The example consists of four files. style.css, add.jsp, delete.jsp and BooksWorker.java.
</p>

<div class="codehead">style.css</div>
<pre class="code">
* { font-size: 12px; font-family: Verdana }

input { border: 1px solid #ccc }

a#currentTab {
    border-bottom:1px solid #fff;
}

a { color: black; text-decoration:none;
    padding:5px; border: 1px solid #aaa;
}

a:hover { background: #ccc }

td { border: 1px solid #ccc; padding: 3px }
th { border: 1px solid #ccc; padding: 3px; 
    background: #009999; color: white }

.navigator { border-bottom:1px solid #aaa; width:300px; padding:5px }
</pre>

<p>
The style.css file defines the style of our application. It defines the look 
and feel of input boxes, buttons, tables and anchors. It also creates a 
navigation toolbar.
</p>

<div class="codehead">index.jsp</div>
<pre class="code">
&lt;%@page contentType="text/html" pageEncoding="UTF-8"%&gt;

&lt;html&gt;
&lt;head&gt;
&lt;meta http-equiv="Content-Type" content="text/html; charset=UTF-8"&gt;
&lt;title&gt;Books database&lt;/title&gt;
&lt;link rel="stylesheet" href="style.css" type="text/css"&gt;
&lt;/head&gt;
&lt;body&gt;

&lt;br&gt;

&lt;div class="navigator"&gt;
&lt;a id="currenttab" href="index.jsp"&gt;Add&lt;/a&gt;
&lt;a href="delete.jsp"&gt;Delete&lt;/a&gt;
&lt;/div&gt;
&lt;%
  String author = request.getParameter("author");
  String title = request.getParameter("title");
  String year = request.getParameter("year");
  String remark = request.getParameter("remark");
  if (author != null &amp;&amp; title != null 
          &amp;&amp; year != null &amp;&amp; remark!= null) {
      com.zetcode.BooksWorker.Insert(author, title, year, remark);
  }
%&gt;

&lt;br&gt; &lt;br&gt; &lt;br&gt;

&lt;form method="post" action="index.jsp"&gt;
&lt;table&gt;
&lt;tr&gt;    
&lt;td&gt;Author&lt;/td&gt;&lt;td&gt;&lt;input type="text" name="author"&gt;&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;Title&lt;/td&gt;&lt;td&gt;&lt;input type="text" name="title"&gt;&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;Year&lt;/td&gt;&lt;td&gt; &lt;input type="text" name="year"&gt;&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;Remark&lt;/td&gt;&lt;td&gt; &lt;input type="text" name="remark"&gt;&lt;/td&gt;
&lt;/tr&gt;
&lt;/table&gt;

&lt;br&gt;
&lt;input type="submit" value="submit"&gt;
&lt;/form&gt;
&lt;/body&gt;
&lt;/html&gt;

</pre>

<p>
The index.jsp file is used to add new books into the database. It defines the form, where we input data.
Upon clicking submit button, it calles itself. 
</p>

<pre class="explanation">
&lt;%
  String author = request.getParameter("author");
  String title = request.getParameter("title");
  String year = request.getParameter("year");
  String remark = request.getParameter("remark");
  if (author != null &amp;&amp; title != null 
          &amp;&amp; year != null &amp;&amp; remark!= null) {
      com.zetcode.BooksWorker.Insert(author, title, year, remark);
  }
%&gt;
</pre>


<p>
This scriptlet will get the parameters from the request. If none of them is 
null, we call the Insert method of the <b>BooksWorker</b> 
class. We put all database related coding into a new class BooksWorker. One of 
the adopted principles in creating web applications is to divide the business logic from
it's presentation layer as much as possible.
</p>



<div class="codehead">delete.jsp</div>
<pre class="code">
&lt;%@page contentType="text/html" pageEncoding="UTF-8"%&gt;
&lt;%@page import="java.util.*" %&gt;


&lt;html&gt;
&lt;head&gt;
&lt;title&gt;Delete&lt;/title&gt;
&lt;link rel="stylesheet" href="style.css" type="text/css"&gt;
        
&lt;/head&gt;
&lt;body&gt;
&lt;%
  Enumeration names = request.getParameterNames();
  while (names.hasMoreElements()) {
      String name = (String) names.nextElement();
      StringBuffer sb = new StringBuffer(name);
      sb.deleteCharAt(0);
      com.zetcode.BooksWorker.Delete(sb.toString());
  }

%&gt;
&lt;br&gt;

&lt;div class="navigator"&gt;
&lt;a href="index.jsp"&gt;Add&lt;/a&gt;
&lt;a id="currenttab" href="delete.jsp"&gt;Delete&lt;/a&gt;
&lt;/div&gt;

&lt;br&gt; &lt;br&gt; &lt;br&gt;

&lt;form action="delete.jsp" method="post"&gt;
&lt;table&gt;
&lt;tr&gt;
&lt;th&gt;Author&lt;/th&gt;
&lt;th&gt;Title&lt;/th&gt;
&lt;th&gt;Year&lt;/th&gt;
&lt;th&gt;Remark&lt;/th&gt;
&lt;/tr&gt; 
&lt;%

  List list = com.zetcode.BooksWorker.GetBooks();
  int id = 0;
  String box = null;

  Iterator&lt;String&gt; it = list.iterator();

  while (it.hasNext()) {
      id = Integer.parseInt(it.next());
      out.print("&lt;tr&gt;");
      for (int i = 0; i &lt; 4; i++) {
          out.print("&lt;td&gt;");
          out.print(it.next());
          out.print("&lt;/td&gt;");
  }
  out.print("&lt;td&gt;");
  box = "&lt;input name=r" + id + " type='checkbox'&gt;";
  out.print(box);
  out.print("&lt;/td&gt;");
  out.print("&lt;/tr&gt;");
 }
%&gt;
 
&lt;/table&gt;
 
&lt;br&gt;
&lt;input type="submit" value="Delete"&gt;

&lt;/form&gt;
 
&lt;/body&gt;
&lt;/html>
</pre>

<p>
The delete.jsp file does two things. It deletes selected items from the 
database table and shows a list of available books. 
</p>

<pre class="explanation">
&lt;%
  Enumeration names = request.getParameterNames();
  while (names.hasMoreElements()) {
      String name = (String) names.nextElement();
      StringBuffer sb = new StringBuffer(name);
      sb.deleteCharAt(0);
      com.zetcode.BooksWorker.Delete(sb.toString());
  }
%&gt;
</pre>

<p>
This scriplet retrieves all parameters from the request. From the selected 
check boxes we receive values r1, r2 ... rn.
Where the r indicates a row and the number is the id of the row in the 
database table. We get the number by calling the <code>deleteCharAt()</code> 
method and call the <code>Delete()</code> method of the BooksWorker class.
</p>


<pre class="explanation">
&lt;%

  List list = com.zetcode.BooksWorker.GetBooks();
  int id = 0;
  String box = null;

  Iterator&lt;String&gt; it = list.iterator();

  while (it.hasNext()) {
      id = Integer.parseInt(it.next());
      out.print("&lt;tr&gt;");
      for (int i = 0; i &lt; 4; i++) {
          out.print("&lt;td&gt;");
          out.print(it.next());
          out.print("&lt;/td&gt;");
  }
  out.print("&lt;td&gt;");
  box = "&lt;input name=r" + id + " type='checkbox'&gt;";
  out.print(box);
  out.print("&lt;/td&gt;");
  out.print("&lt;/tr&gt;");
 }
%&gt;
</pre>

<p>
The second scriptlet builds the table, that displays all items. 
We get all the data by calling the <code>GetBooks()</code> 
method. Next we parse the data and build a table.
</p>

<div class="codehead">BooksWorker.java</div>
<pre class="code">
package com.zetcode;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;


public class BooksWorker {

  static final String url = "jdbc:mysql://localhost:3306/books";

  public static void Insert(String author, String title, 
          String year, String remark) {
      try {

          String insert = "INSERT INTO books(author, title, year, remark)" +
                  "VALUES (?, ?, ?, ?)";

          Class.forName("com.mysql.jdbc.Driver");
          Connection con = DriverManager.getConnection(url, "root", "");
 
          PreparedStatement ps = con.prepareStatement(insert);

          ps.setString(1, author);
          ps.setString(2, title);
          ps.setString(3, year);
          ps.setString(4, remark);
          ps.executeUpdate();
          con.close();

      } catch (Exception ex) {
          Logger.getLogger(BooksWorker.class.getName()).log(
                           Level.SEVERE, null, ex);
      }
  }

  public static List GetBooks() {

      List&lt;String&gt; list = new ArrayList&lt;String&gt;();

      try {

          Class.forName("com.mysql.jdbc.Driver");
          Connection con = DriverManager.getConnection(url, "root", "");

          Statement stmt = con.createStatement();

          ResultSet result = stmt.executeQuery("SELECT * FROM books");

          while(result.next())
          {
             list.add(result.getString("id"));
             list.add(result.getString("author"));
             list.add(result.getString("title"));
             list.add(result.getString("year"));
             list.add(result.getString("remark"));
          } 

          con.close();

      } catch (Exception ex) {
          Logger.getLogger(BooksWorker.class.getName()).log( 
                           Level.SEVERE, null, ex);
      }
          return list;
  }

  public static void Delete(String id) {
      try {
          String delete = "DELETE from books WHERE id = ?";

          Class.forName("com.mysql.jdbc.Driver");
          Connection con = DriverManager.getConnection(url, "root", "");
          PreparedStatement ps = con.prepareStatement(delete);

          ps.setString(1, id);
          ps.executeUpdate();
          con.close();

      } catch (Exception ex) {
          Logger.getLogger(BooksWorker.class.getName()).log( 
             Level.SEVERE, null, ex);
      }
  }
}
</pre>

<p>
We put all the database coding into the BooksWorker class, thus separating model from the view. Here we define
methods for inserting data to the database, deleting data and getting all data from the table.
</p>



<pre class="explanation">
String insert = "INSERT INTO books(author, title, year, remark)" +
    "VALUES (?, ?, ?, ?)";
...
PreparedStatement ps = con.prepareStatement(insert);
ps.setString(1, author);
ps.setString(2, title);
ps.setString(3, year);
ps.setString(4, remark);
ps.executeUpdate();
</pre>

<p>
We use prepared statements. These are <b>parameterized SQL statements</b>. 
The statements use question mark (?) 
as a marker character for passing the parameters. There are two major 
reasons to use parameterized statements.
Securing the code against the SQL injections and easier coding due to 
difficulties with double and single quotes. 
</p>

<pre class="explanation">
} catch (Exception ex) {
    Logger.getLogger(BooksWorker.class.getName()).log(
                    Level.SEVERE, null, ex);
}
</pre>

<p>
If there is some exception, we log a message about it. The messages 
can be viewed in a log file, which is located in domains/domain1/logs/serve.log 
file of the glassfish root directory. 
</p>


<p>
We have put most of the business code of the application outside 
the JSP files. But there is still some
code left, which would be better to put elsewhere. We will manage 
this later, when we will talk about <b>servlets</b>
and <b>custom jsp tags</b>.
</p>


<h3>Using ant to build and deploy the web application.</h3>

<br>
<img src="/img/java/jee/add.png" alt="Add">
<div class="figure">Figure: index.jsp</div>
<br>

<br>
<img src="/img/java/jee/delete.png" alt="Delete">
<div class="figure">Figure: delete.jsp</div>
<br>


<p>
In this chapter, we worked with MySQL from JSP pages.
</p>

<div class="center"> 
<script type="text/javascript"><!--
google_ad_client = "pub-9706709751191532";
/* horizontal */
google_ad_slot = "1734478269";
google_ad_width = 468;
google_ad_height = 60;
//-->
</script> 
<script type="text/javascript"
src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> 
</script> 
</div> 
<br> 


<div class="botNav, center">
<span class="botNavItem"><a href="/">Home</a></span> ‡ <span class="botNavItem"><a href="..">Contents</a></span> ‡ 
<span class="botNavItem"><a href="#">Top of Page</a></span>
</div>


<div class="footer">
<div class="signature">
<a href="/">ZetCode</a> last modified January 8, 2008  <span class="copyright">&copy; 2007 - 2013 Jan Bodnar</span>
</div>
</div>

</div> <!-- content -->

</div> <!-- container -->

</body>
</html>

